package com.hongkou.tvstation.image.dao;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.hongkou.tvstation.common.utils.DateUtils;
import com.hongkou.tvstation.common.utils.StringUtils;
import com.hongkou.tvstation.image.data.TImage;
import com.hongkou.tvstation.image.vo.ImageTypeVO;
import com.hongkou.tvstation.image.vo.ImageVO;
import com.hongkou.tvstation.pub.hibernate.GenericDaoSupport;

public class ImageDao extends GenericDaoSupport {

	public List<ImageTypeVO> loadImageType() throws SQLException {
		List<ImageTypeVO> list = new ArrayList<ImageTypeVO>();
		Connection connection = getMySqlConnection();
		Statement statement = null;
		ResultSet resultSet = null;
		try {
			statement = connection.createStatement();
			resultSet = statement
					.executeQuery("select type_id,type_name from t_image_type");
			while (resultSet.next()) {
				ImageTypeVO imageTypeVO = new ImageTypeVO();
				int pos = 1;
				imageTypeVO.setTypeId(resultSet.getInt(pos++));
				imageTypeVO.setTypeName(resultSet.getString(pos++));
				list.add(imageTypeVO);
			}
		} catch (SQLException e) {
			throw e;
		} finally {
			close(resultSet, statement, connection);
		}

		return list;
	}

	/*
	 * public List<TImage> listImage(ImageVO queryVO) {
	 * 
	 * List<Criterion> criterions = new ArrayList<Criterion>(); if
	 * (queryVO.getType() != null) { criterions.add(Restrictions.eq(TImage.TYPE,
	 * queryVO.getType())); } Criteria criteria = getseSession()
	 * .createCriteria(getPersistencyClass()); if (!criterions.isEmpty()) { for
	 * (Criterion criterion : criterions) { criteria.add(criterion); } } List<TImage>
	 * list = (List<TImage>) criteria.list(); return list; }
	 */
	public void deleteImage(Long imageId) throws SQLException {
		Connection connection = getMySqlConnection();
		PreparedStatement statement = null;
		ResultSet resultSet = null;
		try {
			connection.setAutoCommit(false);
			statement = connection
					.prepareStatement("delete from t_image where image_id=?");
			int pos = 1;
			statement.setLong(pos++, imageId);
			statement.executeUpdate();
			connection.commit();
		} catch (SQLException e) {
			throw e;
		} finally {
			close(resultSet, statement, connection);
		}
	}

	public void saveImage(ImageVO imageVO) throws SQLException {
		TImage tImage = fillImage(imageVO);
		Connection connection = getMySqlConnection();
		PreparedStatement statement = null;
		ResultSet resultSet = null;
		try {
			connection.setAutoCommit(false);
			statement = connection
					.prepareStatement("insert into t_image(image_name,image_desc,file_name,category,file_path,shooting_time,image_group,key_word) values(?,?,?,?,?,?,?,?)");
			int pos = 1;
			statement.setString(pos++, tImage.getImageName());
			statement.setString(pos++, tImage.getImageDesc());
			statement.setString(pos++, tImage.getFileName());
			statement.setLong(pos++, tImage.getCategoryId());
			statement.setString(pos++, tImage.getFilePath());
			Date sqlDate = null;
			if (imageVO.getShootingTime() != null) {
				sqlDate = DateUtils.toSqlDate(imageVO.getShootingTime());
			}
			statement.setDate(pos++, sqlDate);
			statement.setInt(pos++, tImage.getImageGroup());
			statement.setString(pos++, tImage.getKeyWord());
			statement.executeUpdate();
			connection.commit();
		} catch (SQLException e) {
			throw e;
		} finally {
			close(resultSet, statement, connection);
		}
	}

	private TImage fillImage(ImageVO imageVO) {
		TImage tImage = new TImage();
		tImage.setImageName(imageVO.getImageName());
		tImage.setFileName(imageVO.getFileName());
		tImage.setFilePath(imageVO.getFilePath());
		tImage.setType(imageVO.getType());
		tImage.setCategoryId(imageVO.getCategory());
		tImage.setImageDesc(imageVO.getImageDesc());
		tImage.setFilePath(imageVO.getFileName());
		tImage.setShootingTime(imageVO.getShootingTime());
		tImage.setImageGroup(imageVO.getImageGroup());
		tImage.setKeyWord(imageVO.getKeyWord());
		return tImage;
	}

	public List<TImage> findImages(ImageVO queryVO) throws SQLException {
		List<TImage> list = new ArrayList<TImage>();
		Connection connection = getMySqlConnection();
		Statement statement = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		try {
			statement = connection.createStatement();
			preparedStatement = connection
					.prepareStatement("select count(1) from t_image"
							+ getWhereSql(queryVO));
			int pos = 1;
			if (queryVO.getShootingStartTime() != null) {
				preparedStatement.setDate(pos++, DateUtils.toSqlDate(queryVO
						.getShootingStartTime()));
			}
			if (queryVO.getShootingEndTime() != null) {
				preparedStatement.setDate(pos++, DateUtils.toSqlDate(queryVO
						.getShootingEndTime()));
			}
			resultSet = preparedStatement.executeQuery();
			resultSet.next();
			queryVO.getPageVO().setTotalRecords(
					Long.valueOf(resultSet.getInt(1)));
			close(resultSet, statement, null);
			String sql = "select image_id,image_name,image_desc,file_name,category,file_path,shooting_time,image_group,key_word from t_image"
					+ getWhereSql(queryVO)
					+ " order by image_id desc limit "
					+ ((queryVO.getPageVO().getPageNum() - 1) * queryVO
							.getPageVO().getPageSize())
					+ ","
					+ queryVO.getPageVO().getPageSize();
			preparedStatement = connection.prepareStatement(sql);
			pos = 1;
			if (queryVO.getShootingStartTime() != null) {
				preparedStatement.setDate(pos++, DateUtils.toSqlDate(queryVO
						.getShootingStartTime()));
			}
			if (queryVO.getShootingEndTime() != null) {
				preparedStatement.setDate(pos++, DateUtils.toSqlDate(queryVO
						.getShootingEndTime()));
			}
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				TImage tImage = fillImage(resultSet);
				list.add(tImage);
			}
		} catch (SQLException e) {
			throw e;
		} finally {
			close(null, preparedStatement, null);
			close(resultSet, statement, connection);
		}

		return list;
	}

	private String getWhereSql(ImageVO queryVO) {
		StringBuffer whereSql = new StringBuffer("");
		if (!StringUtils.isNullOrEmpty(queryVO.getImageName())) {
			whereSql.append(" and image_name like '%" + queryVO.getImageName()
					+ "%'");
		}
		if (queryVO.getCategory() != null && queryVO.getCategory() != 0L) {
			whereSql.append(" and category = " + queryVO.getCategory());
		}
		if (queryVO.getType() != null && queryVO.getType() != 0L) {
			whereSql.append(" and type = " + queryVO.getType());
		}
		if (queryVO.getImageGroup() != null && queryVO.getImageGroup() != 0) {
			whereSql.append(" and image_group = " + queryVO.getImageGroup());
		}
		if (queryVO.getShootingStartTime() != null) {
			whereSql.append(" and shooting_time >= ?");
		}
		if (queryVO.getShootingEndTime() != null) {
			whereSql.append(" and shooting_time <= ?");
		}
		return whereSql.toString().replaceFirst(" and ", " where ");
	}

	private TImage fillImage(ResultSet resultSet) throws SQLException {
		TImage tImage = new TImage();
		int pos = 1;
		tImage.setImageId(resultSet.getLong(pos++));
		tImage.setImageName(resultSet.getString(pos++));
		tImage.setImageDesc(resultSet.getString(pos++));
		tImage.setFileName(resultSet.getString(pos++));
		tImage.setCategoryId(resultSet.getLong(pos++));
		tImage.setFilePath(resultSet.getString(pos++));
		tImage.setShootingTime(resultSet.getDate(pos++));
		tImage.setImageGroup(resultSet.getInt(pos++));
		tImage.setKeyWord(resultSet.getString(pos++));
		return tImage;
	}

	public List findRelatedNews(Long imageId) throws SQLException {
		Connection connection = getMySqlConnection();
		Statement statement = null;
		ResultSet resultSet = null;
		List newIds = new ArrayList();
		try {
			String sql = "select * from t_image_news where image_id=" + imageId;
			statement = connection.createStatement();
			resultSet = statement.executeQuery(sql);
			while (resultSet.next()) {
				newIds.add(resultSet.getLong("news_id"));
			}
		} catch (SQLException e) {
			throw e;
		} finally {
			close(resultSet, statement, connection);
		}
		return newIds;
	}

	public TImage findImages(Long imageId) throws SQLException {
		TImage tImage = null;
		Connection connection = getMySqlConnection();
		Statement statement = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		try {
			statement = connection.createStatement();
			String sql = "select image_id,image_name,image_desc,file_name,category,file_path,shooting_time,image_group,key_word from t_image where image_id="
					+ imageId;
			preparedStatement = connection.prepareStatement(sql);
			resultSet = preparedStatement.executeQuery();
			if (resultSet.next()) {
				tImage = fillImage(resultSet);
			}
		} catch (SQLException e) {
			throw e;
		} finally {
			close(null, preparedStatement, null);
			close(resultSet, statement, connection);
		}

		return tImage;
	}

	public void relateToNew(Long newsId, Long imageId) throws SQLException {
		Connection connection = getMySqlConnection();
		PreparedStatement statement = null;
		try {
			statement = connection
					.prepareStatement("insert into t_image_news(image_id,news_id) values (?,?)");
			int point = 1;
			statement.setInt(point++, imageId.intValue());
			statement.setInt(point++, newsId.intValue());
			statement.executeUpdate();
		} catch (SQLException e) {
			throw e;
		} finally {
			close(null, statement, connection);
		}
	}

	public void removeImageRelated(Long imageId) throws SQLException {
		Connection connection = getMySqlConnection();
		PreparedStatement statement = null;
		try {
			statement = connection
					.prepareStatement("delete from t_image_news where image_id=?");
			int point = 1;
			statement.setInt(point++, imageId.intValue());
			statement.executeUpdate();
		} catch (SQLException e) {
			throw e;
		} finally {
			close(null, statement, connection);
		}
	}

	public boolean checkExistsImageGroup(String imageGroupName)
			throws SQLException {
		Connection connection = getMySqlConnection();
		Statement statement = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		try {
			statement = connection.createStatement();
			String sql = "select * from T_IMAGE_GROUP where group_name='"
					+ imageGroupName + "'";
			preparedStatement = connection.prepareStatement(sql);
			resultSet = preparedStatement.executeQuery();
			if (resultSet.next()) {
				return true;
			}
		} catch (SQLException e) {
			throw e;
		} finally {
			close(null, preparedStatement, null);
			close(resultSet, statement, connection);
		}

		return false;
	}

	public void saveGroup(String imageGroup) throws SQLException {
		Connection connection = getMySqlConnection();
		PreparedStatement statement = null;
		try {
			statement = connection
					.prepareStatement("insert into t_image_group(group_name) values (?)");
			int point = 1;
			statement.setString(point++, imageGroup);
			statement.executeUpdate();
		} catch (SQLException e) {
			throw e;
		} finally {
			close(null, statement, connection);
		}
	}
}
